#include "mysql_util.h"
#include <cstdio> // wz add
using namespace std;

MYSQL* conn = NULL;

void initialize()
{
    while(!initDB("47.115.168.233", "root", "jrvv_NSYW_b2b", "xinzhike_company2"));
}

bool initDB(const string& host, const string& user, const string& passwd, const string& db_name, const int port) 
{
	conn = mysql_init(conn);
	if(conn == NULL) {
		cout << "Error: " << mysql_error(conn);
        return false;
	}
	if(!mysql_real_connect(conn, host.c_str(), user.c_str(), passwd.c_str(), db_name.c_str(), port, NULL, 0)) {
		cout << "Error: " << mysql_error(conn);
        return false;
	}
	mysql_query(conn, "set names utf8");
	cout << "Connect successfully." << endl;
	return true;
}

bool startTrans(){
	return execQuery("START TRANSACTION");
}

bool execQuery(const string& sql, string* msg) 
{
	if (mysql_query(conn, sql.c_str()) != 0)	//执行失败返回非0值
    {
        cout<< "Query Error: " << mysql_error(conn);
        return false;
    }
    MYSQL_RES* result = NULL;
    MYSQL_ROW rows;
    result = mysql_store_result(conn);
    if(result != NULL){
    	//有结果返回，说明是select语句
    	//必须要保证msg不是NULL，才能返回select的结果
    	if(msg == NULL){
    		cout << "You should pass a string pointer to store result." << endl;
    		mysql_free_result(result);
    		return false;
    	}
    	int num_fields = mysql_num_fields(result);   //获取结果集中总共的字段数，即列数
		int num_rows = mysql_num_rows(result);       //获取结果集中总共的行数
		for(int i = 0; i < num_rows; ++i)
		{
			//获取下一行数据
			rows = mysql_fetch_row(result);
			if(rows < 0) break;
			for(int j = 0; j < num_fields; j++)
			{
			    *msg += string(rows[j] == NULL ? "NULL" : rows[j]);
			    if (j == num_fields - 1) *msg += "\n";
			    else *msg += "\t";
			}
		}
    }
    else{
    	//result == NULL，说明可能是update、delete、insert等语句
    	if(mysql_field_count(conn) == 0){
    		//返回最近查询结果的列数，如果为0，说明执行的是update、delete、insert等语句
    		int num_rows = mysql_affected_rows(conn);  //返回update,insert,delete影响的行数
    	}
    	else{
    		//不然就是出错了
    		cout << "Get result error: " << mysql_error(conn);
            return false;
    	}
    }
    mysql_free_result(result);
    return true;
}

bool commitTrans()
{
	return execQuery("COMMIT");
}

void closeDB() 
{
    if(conn != NULL){
       mysql_close(conn); 
    }
	mysql_library_end();
}


/***********提供给打卡机的函数接口**************/
string getNow(DateTimeFormat format)
{
    string fm;
    switch (format)
    {
    case DATE:
        fm = "%Y-%m-%d";
        break;
    case TIME:
        fm = "%H:%M:%S";
        break;
    case DATETIME:
        fm = "%Y-%m-%d %H:%M:%S";
        break;
    }
    time_t now = 0;
    time(&now);
    char tmp[64];
    strftime(tmp, sizeof(tmp), fm.c_str(), localtime(&now));
    return tmp;
}

void getNow(DateTimeFormat format, char ret[])
{
    string fm;
    switch (format)
    {
    case DATE:
        fm = "%Y-%m-%d";
        break;
    case TIME:
        fm = "%H:%M:%S";
        break;
    case DATETIME:
        fm = "%Y-%m-%d %H:%M:%S";
        break;
    }
    time_t now = 0;
    time(&now);
    char tmp[64];
    strftime(tmp, sizeof(tmp), fm.c_str(), localtime(&now));
    strcpy(ret, tmp);
}

bool insertPunchInTime(const string& id, const string& tm)
{
    if(id.empty()) {
        cout << "员工id信息不能为空" << endl;
        return false;
    }
    char tmp[MAX_SQL_LENTH];
    string today = getNow(DATE);
    sprintf(tmp, "INSERT INTO `card_info`(`staff_id`, `today`, `punch_in_time`) VALUES ('%s', '%s', '%s');", 
        id.c_str(), today.c_str(), tm.c_str());
    return execQuery(tmp);
}

bool isPunchIn(const string& id)
{
    if(id.empty()) {
        cout << "员工id信息不能为空" << endl;
        return false;
    }
    char tmp[MAX_SQL_LENTH];
    string today = getNow(DATE);
    sprintf(tmp, "select * from `card_info` where `staff_id` = '%s' and `today` = '%s';", 
        id.c_str(), today.c_str());
    string msg;
    bool flag = execQuery(tmp, &msg);
    if(!flag || msg.empty()) return false;
    return true;
}

bool insertPunchOutTime(const string& id, const string& tm)
{
    if(id.empty()) {
        cout << "员工id信息不能为空" << endl;
        return false;
    }
    char tmp[MAX_SQL_LENTH];
    string today = getNow(DATE);
    sprintf(tmp, "update `card_info` set `punch_out_time` = '%s' where `staff_id` = '%s' and `today` = '%s';", 
        tm.c_str(), id.c_str(), today.c_str());
    return execQuery(tmp);
}

bool insertDailyCardInfo(const card_info& ci)
{
    if(ci.staff_id.empty()) {
        cout << "员工id信息不能为空" << endl;
        return false;
    }
    char tmp[MAX_SQL_LENTH];
    string today = getNow(DATE);
    sprintf(tmp, "update `card_info` set `is_late` = %d, `is_leave_early` = %d, `is_miss` = %d, `is_absenteeism` = %d, `work_time` = %d where `staff_id` = '%s' and `today` = '%s';", 
        ci.is_late, ci.is_leave_early, ci.is_miss, ci.is_absenteeism, ci.work_time, ci.staff_id.c_str(), today.c_str());
    return execQuery(tmp);
}

bool queryCardInfo(card_info& ci)
{
    if(ci.staff_id.empty()) {
        cout << "员工id信息不能为空" << endl;
        return false;
    }
    if(ci.today.empty()) ci.today = getNow(DATE);
    char tmp[MAX_SQL_LENTH];
    sprintf(tmp, "select `punch_in_time`, `punch_out_time`, `is_late`, `is_leave_early`, `is_miss`, `is_absenteeism`, `work_time` from `card_info` where `staff_id` = '%s' and `today` = '%s';",
        ci.staff_id.c_str(), ci.today.c_str());
    string msg;
    bool flag = execQuery(tmp, &msg);
    if(!flag || msg.empty()) return false;

    //切分数据
    string::iterator iter = msg.begin();
    size_t start = 0, pos = 0;

    pos = msg.find('\t', pos);
    ci.punch_in_time.assign(iter + start, iter + pos);
    start = ++pos;

    pos = msg.find('\t', pos);
    ci.punch_out_time.assign(iter + start, iter + pos);
    start = ++pos;

    pos = msg.find('\t', pos);
    string is_late(iter + start, iter + pos);
    ci.is_late = atoi(is_late.c_str());
    start = ++pos;

    pos = msg.find('\t', pos);
    string is_leave_early(iter + start, iter + pos);
    ci.is_leave_early = atoi(is_leave_early.c_str());
    start = ++pos;

    pos = msg.find('\t', pos);
    string is_miss(iter + start, iter + pos);
    ci.is_miss = atoi(is_miss.c_str());
    start = ++pos;

    pos = msg.find('\t', pos);
    string is_absenteeism(iter + start, iter + pos);
    ci.is_absenteeism = atoi(is_absenteeism.c_str());
    start = ++pos;

    pos = msg.find('\t', pos);
    string work_time(iter + start, msg.end());
    ci.work_time = atoi(work_time.c_str());
    return true;
} 


/***********提供给职员系统的函数接口**************/
bool insertStaff(const staff& s)
{
    char tmp[MAX_SQL_LENTH];
    sprintf(tmp, "INSERT INTO `staff`(`staff_id`, `tel`, `mail`, `name`, `sex`, `age`, `rank`) VALUES ('%s', '%s', '%s', '%s', %d, %d, %d);", 
        s.id.c_str(), s.tel.c_str(), s.mail.c_str(), s.name.c_str(), s.sex, s.age, s.rank);
    return execQuery(tmp);
}

bool queryStaffById(const string& id, staff& s)
{
    string msg;
    char tmp[MAX_SQL_LENTH];
    sprintf(tmp, "select * from `staff` where `staff_id` = '%s';", id.c_str());
    bool flag = execQuery(tmp, &msg);
    if(!flag || msg.empty()) return false;
    string::iterator iter = msg.begin();
    s.id = id;

    //生成验证码，类型为string
    const char* tmp_wz = &id.c_str()[1];
    string code = tmp_wz;
    code.assign(code.rbegin(), code.rend());    //反转字符串
    size_t code_len = code.size();
    int vc = atoi(code.c_str()) + atoi(tmp_wz);
    size_t vc_len = to_string(vc).size();
    //合成验证码
    s.ver_code = id.c_str()[0];
    for(size_t i = 0; i < code_len - vc_len; ++i){
        s.ver_code += '0';
    }
    s.ver_code += to_string(vc); 

    //切分数据
    size_t start = 0, pos = 0;
    pos = msg.find('\t', pos);
    start = ++pos;
    //忽略id
    pos = msg.find('\t', pos);
    s.tel.assign(iter + start, iter + pos);
    start = ++pos;

    pos = msg.find('\t', pos);
    s.mail.assign(iter + start, iter + pos);
    start = ++pos;

    pos = msg.find('\t', pos);
    s.name.assign(iter + start, iter + pos);
    start = ++pos;

    pos = msg.find('\t', pos);
    string sex(iter + start, iter + pos);
    if(sex == "male") s.sex = male;
    else if(sex == "female") s.sex = female;
    else s.sex = none;
    start = ++pos;

    pos = msg.find('\t', pos);
    string age(iter + start, iter + pos);
    s.age = atoi(age.c_str());
    start = ++pos;

    pos = msg.find('\t', pos);
    string rank(iter + start, iter + pos);
    if(rank == "CEO") s.rank = CEO;
    else if(rank == "Vice_President") s.rank = Vice_President;
    else if(rank == "Department_manager") s.rank = Department_manager;
    else s.rank = Employee;
    start = ++pos;

    string is_deleted(iter + start, msg.end());
    s.is_deleted = atoi(is_deleted.c_str());
    return true;
}

bool queryAllStaffs(string& msg, bool without_deleted)
{
    char tmp[MAX_SQL_LENTH];
    sprintf(tmp, "select * from `staff` where `is_deleted` = '%d';", !without_deleted);
    return execQuery(tmp, &msg);
}

bool deleteStaffById(const string& id)
{
    char tmp[MAX_SQL_LENTH];
    sprintf(tmp, "update `staff` set `is_deleted` = true where `staff_id` = '%s';", id.c_str());
    return execQuery(tmp);
}

int queryAbnormalCardInfo(const string& id, vector<card_info>& ret)
{
    //vector<card_info> ret;
    if(id.empty()) {
        cout << "员工id信息不能为空" << endl;
        return -1;
    }

    char tmp[MAX_SQL_LENTH];
        sprintf(tmp, "select * from `card_info` where `staff_id` = '%s' and date_format(`today`,'%%Y-%%m') = date_format(curdate(),'%%Y-%%m') and (`is_late` = true or `is_leave_early` = true or `is_miss` = true or `is_absenteeism` = true)",
        id.c_str());
    string msg;
    bool flag = execQuery(tmp, &msg);
    if(!flag) return -1;
    if(msg.empty()) return 0;

    vector<string> infos;
    size_t start = 0, pos = 0;
    string::iterator iter = msg.begin();
    while ((pos = msg.find('\n', pos)) != string::npos) {
        string info(iter + start, iter + pos);
        start = ++pos;
        infos.push_back(info);
    }
    
    for(size_t i = 0; i < infos.size(); ++i){
        start = 0, pos = 0;
        iter = infos[i].begin();
        card_info temp;

        //忽略staff_id
        pos = msg.find('\t', pos);
        start = ++pos;
        
        //从today开始切割
        pos = infos[i].find('\t', pos);
        temp.today.assign(iter + start, iter + pos);
        start = ++pos;

        pos = infos[i].find('\t', pos);
        temp.punch_in_time.assign(iter + start, iter + pos);
        start = ++pos;

        pos = infos[i].find('\t', pos);
        temp.punch_out_time.assign(iter + start, iter + pos);
        start = ++pos;

        pos = infos[i].find('\t', pos);
        string is_late(iter + start, iter + pos);
        temp.is_late = atoi(is_late.c_str());
        start = ++pos;

        pos = infos[i].find('\t', pos);
        string is_leave_early(iter + start, iter + pos);
        temp.is_leave_early = atoi(is_leave_early.c_str());
        start = ++pos;

        pos = infos[i].find('\t', pos);
        string is_miss(iter + start, iter + pos);
        temp.is_miss = atoi(is_miss.c_str());
        start = ++pos;

        pos = infos[i].find('\t', pos);
        string is_absenteeism(iter + start, iter + pos);
        temp.is_absenteeism = atoi(is_absenteeism.c_str());
        start = ++pos;

        pos = infos[i].find('\t', pos);
        string work_time(iter + start, infos[i].end());
        temp.work_time = atoi(work_time.c_str());

        ret.push_back(temp);
    }
    return ret.size();
}

bool updateCardInfo(const card_info& ci)
{
    if(ci.staff_id.empty()) {
        cout << "员工id信息不能为空" << endl;
        return false;
    }
    if(ci.today.empty()) {
        cout << "补卡日期不能为空" << endl;
        return false;
    }

    char tmp[MAX_SQL_LENTH];
    sprintf(tmp, "update `card_info` set `punch_in_time` = '%s', `punch_out_time` = '%s', `is_late` = %d, `is_leave_early` = %d, `is_miss` = %d, `is_absenteeism` = %d where `staff_id` = '%s' and `today` = '%s'",
        ci.punch_in_time.c_str(), ci.punch_out_time.c_str(), ci.is_late, ci.is_leave_early, ci.is_miss, ci.is_absenteeism, ci.staff_id.c_str(), ci.today.c_str());
    return execQuery(tmp);
}

bool sql_deduct_fixed(string id,float deduct)//deduct:扣除的钱 writed by joker, wz add
{
	char tmp[256];
    float temp;
    sprintf(tmp, "select fixed from `salary` where staff_id='%s';",id.c_str());
	const char* sql=tmp;
	if (mysql_query(conn, sql) != 0)	//执行失败返回非0值
    {
        cout<< "Query Error: " << mysql_error(conn);
        return false;
    }
	MYSQL_RES* result = NULL;
    MYSQL_ROW rows;
    result = mysql_store_result(conn);
	if(result != NULL){
		rows = mysql_fetch_row(result);
		temp=atof(rows[0]);
    }
    else
		return false;
    sprintf(tmp, "update salary set fixed=%f where staff_id='%s';",temp-deduct,id.c_str());
	return execQuery(tmp);
}



/***********提供给项目系统的函数接口**************/
/*前期要存入数据库的信息
project_name、project_fund、project_team_id、team_percent、
team_member_id
work_percent、project_start_t、project_end_t、 project_period 、
test_note_time
*/

bool query_all_staff_id(string& msg ,bool without_deleted )
{
    char tmp[256];
    sprintf(tmp, "select `staff_id`, `name` from `staff` where `is_deleted` = %d;", without_deleted );
    return execQuery(tmp, &msg);
}

bool insert_project_manage(const Project_message& s)
{
    char tmp[256];
    sprintf(tmp, "INSERT INTO `project_message`(`project_name`,`project_fund`) VALUES ('%s',%f);",
     s.project_name.c_str(),s.project_fund);
    return execQuery(tmp);
}

bool update_project_manage(const Project_message& s)
{
    char tmp[256]; 
    sprintf(tmp, "UPDATE `project_message` SET  `project_start_t` = '%s',`project_end_t` = '%s',`project_period`= %d,`team_num`= %d, `node_num` = %d WHERE `project_name`= '%s' ;",
    s.project_start_t.c_str(),s.project_end_t.c_str(),s.project_period,s.team_num, s.node_num, s.project_name.c_str());
    return execQuery(tmp);
}



bool insert_project_team_develop(const Project_Team& s)
{
    char tmp[256];
    sprintf(tmp, "INSERT INTO `develop`(`project_name`,`team_id`) VALUES ('%s','%s');",
    s.project_name.c_str(), s.team_id.c_str());
    return execQuery(tmp);
}

bool update_project_team_develop(const Project_Team& s)
{
    char tmp[256]; 
    sprintf(tmp, "UPDATE `develop` SET  `team_percent` = %f,`team_member_num` = %d WHERE `project_name` = '%s'AND`team_id` = '%s';",
    s.team_percent,s.team_member_num,s.project_name.c_str(), s.team_id.c_str());
    return execQuery(tmp);
}


bool insert_project_team(const Project_member& s )
{
    char tmp[256];
    sprintf(tmp, "INSERT INTO `have`(`team_id` ,`work_precent`, `staff_id`) VALUES ( '%s' , %f, '%s');",
      s.team_id.c_str(),s.work_percent,s.id.c_str());
     return execQuery(tmp);
}

bool insert_project_test(const string& project_name,const string&test_note_time)
{  
    char tmp[256];  
    sprintf(tmp, "INSERT INTO `check_info`(`project_name`,`test_note_time`) VALUES ('%s', '%s');", 
    project_name.c_str(),test_note_time.c_str());
     return execQuery(tmp);
}

/*中期要读取的数据库的信息
test_note_time
中期要存入数据库的信息
monitor_result\test_result\rejectionTimes\check_accep_result
*/

bool query_test_note_time(string& msg ,const string& project_name )
{
    char tmp[256];
    sprintf(tmp, "select `project_name`, `test_note_time` from `check_info` where `project_name` = '%s' ;" ,  project_name.c_str());
    return execQuery(tmp, &msg);
}


bool update_monitor_result(const string& project_name,const string&test_note_time, int monitor_result)
{
    char tmp[256];
    sprintf(tmp, "update `check_info` set `monitor_result` = %d where `project_name` = '%s' AND `test_note_time`= '%s' ; " ,  monitor_result, project_name.c_str(),test_note_time.c_str());
    return execQuery(tmp);
}

bool update_test_result(const string& project_name,const string& test_note_time,int test_result)
{
    char tmp[256]; 
    sprintf(tmp, "UPDATE `check_info` SET  `test_result` = %d WHERE `project_name`= '%s' AND `test_note_time` = '%s';",test_result,project_name.c_str(),test_note_time.c_str());
    return execQuery(tmp);
}

bool query_test_result(const string& project_name,const string& test_note_time,int &test_result)
{
    string msg;
    char tmp[256];
    sprintf(tmp, "select `test_result` from `check_info` where `project_name` = '%s' AND `test_note_time` = '%s';", project_name.c_str(),test_note_time.c_str());
    if(!execQuery(tmp, &msg)) return false; 

    string t = msg.c_str();
    test_result = atoi(t.c_str());
    return true;
}

bool update_check_accep_result(const string& project_name,const string& test_note_time,int check_accep_result)
{
    char tmp[256]; 
    sprintf(tmp, "UPDATE `check_info` SET  `check_accep_result` = %d WHERE `project_name`= '%s' AND `test_note_time` = '%s';",
    check_accep_result,project_name.c_str(),test_note_time.c_str());
    return execQuery(tmp);
}

bool update_rejectionTimest(const string& project_name,int rejectionTimes )
{
    char tmp[256]; 
    sprintf(tmp, "UPDATE `project_message` SET  `rejectionTimes` = %d WHERE `project_name`= '%s' ;",
    rejectionTimes,project_name.c_str());
    return execQuery(tmp);
}

bool query_rejectionTimes(const string& project_name,int &rejectionTimes )
{
    string msg;
    char tmp[256];
    sprintf(tmp, "select `rejectionTimes` from `project_message` where `project_name` = '%s' ;", project_name.c_str());
    if(!execQuery(tmp, &msg)) return false;     

    rejectionTimes = atoi(msg.c_str());
    return true;
}

/*后期要读取的
project_end_t\rejectionTimes\project_fund\project_team_id\
team_percent\team_member_num\work_percent

后期要存入数据库的信息
post_time\coefficient\ details       
team_earnings\project_earnings\worker_earnings
*/
bool query_project_message(const string& project_name, Project_message& s)
{
    string msg;
    char tmp[256];
    sprintf(tmp, "select `project_fund`,`project_end_t`,`team_num`,`rejectionTimes` from `project_message` where `project_name` = '%s';", project_name.c_str());
    if(!execQuery(tmp, &msg)) return false;
    string::iterator iter = msg.begin();
    s.project_name = project_name;
    //切分数据
    size_t start = 0, pos = 0;

    pos = msg.find('\t', pos);
    string project_fund(iter + start, iter + pos);
    s.project_fund = atof(project_fund.c_str());
    start = ++pos;

    pos = msg.find('\t', pos);
    s.project_end_t.assign(iter + start, iter + pos);
    start = ++pos;

    pos = msg.find('\t', pos);
    string team_num(iter + start, iter + pos);
    s.team_num = atoi(team_num.c_str());
    start = ++pos;

    pos = msg.find('\t', pos);
    string rejectionTimes(iter + start,  msg.end());
    s.rejectionTimes = atoi(rejectionTimes.c_str()); 
    start = ++pos;

    return true;
}


bool query_all_team_id(string& msg ,const string& project_name )
{
    char tmp[256];
    sprintf(tmp, "select `project_name`, `team_id` from `develop` where `project_name` = '%s' ;", 
    project_name.c_str());
    return execQuery(tmp, &msg);
}

bool query_team_percent(Project_Team& s,const string& team_id,const string &project_name)
{
    string msg;
    char tmp[256];
    sprintf(tmp, "select `team_percent`,`team_member_num` from `develop` where `project_name` = '%s' AND `team_id` = '%s';", project_name.c_str(),team_id.c_str());
    if(!execQuery(tmp, &msg)) return false;
    string::iterator iter = msg.begin();    

    size_t start = 0, pos = 0;
    pos = msg.find('\t', pos);
    string project_fund(iter + start, iter + pos);
    s.team_percent = atof(project_fund.c_str());
    start = ++pos;

    pos = msg.find('\t', pos);
    string team_member_num(iter + start,  msg.end());
    s.team_member_num = atoi(team_member_num.c_str()); 
    start = ++pos;
 
    return true;
}

bool query_work_percent(const string& id,const string& team_id,float &work_precent)
{
    string msg;
    char tmp[256];
    sprintf(tmp, "select `work_precent` from `have` where `staff_id` = '%s' and `team_id` = '%s';", id.c_str(),team_id.c_str());
    if(!execQuery(tmp, &msg)) return false;
      

    work_precent = atof(msg.c_str());
    return true;
}

//结项

bool update_projectpost(const Project_message& s)
{
    char tmp[256]; 
 
    sprintf(tmp, "UPDATE `project_message` SET  `coefficient` = %f,`post_time`='%s',`details`='%s' WHERE `project_name`= '%s' ;",s.coefficient,s.post_time.c_str(),s.details.c_str(),s.project_name.c_str());
    return execQuery(tmp);
}


bool update_project_earnings(const Project_message& s)
{
    char tmp[256]; 
   
    sprintf(tmp, "UPDATE `project_message` SET  `project_earnings` = %f WHERE `project_name`= '%s' ;",s.project_earnings,s.project_name.c_str());
    return execQuery(tmp);
}

bool update_team_earnings(const Project_Team& s,const string& project_name)
{
    char tmp[256]; 
    
    sprintf(tmp, "UPDATE `develop` SET  `team_earnings` = %f WHERE `team_id`= '%s' AND `project_name`= '%s';",s.team_earnings,s.team_id.c_str(),project_name.c_str());
    cout << "update_team_earnings"<< ' '<< s.team_earnings<< ' '<< "s.team_id"<< ' '<< s.team_id<< ' '<< project_name<<endl;
    return execQuery(tmp);
}

bool update_worker_earnings(const string & staff_id,const string& team_id, float worker_earnings)
{
    char tmp[256]; 
    sprintf(tmp, "UPDATE `have` SET  `worker_earnings` = %f WHERE `staff_id`= '%s' AND `team_id`='%s';",worker_earnings,staff_id.c_str(),team_id.c_str());
    return execQuery(tmp);
}


/***********提供给财务系统的函数接口**************/
//水费
bool sql_record_water(struct water &wr)
{
    char tmp[256];
    sprintf(tmp, "INSERT INTO `water`(`record_time`, `amount`, `price`, `fee`) VALUES ('%s', %f, %f, %f);", 
        wr.record_time,wr.amount, wr.price,wr.fee);
     return execQuery(tmp);
}

bool sql_show_water(struct water &wr)
{
	char tmp[256];
    sprintf(tmp, "select * from `water` where record_time = '%s';", wr.record_time);
	const char* sql=tmp;
	if (mysql_query(conn, sql) != 0)	//执行失败返回非0值
    {
        cout<< "Query Error: " << mysql_error(conn);
        return false;
    }
	MYSQL_RES* result = NULL;
    MYSQL_ROW rows;
    result = mysql_store_result(conn);
	if(result != NULL){
		rows = mysql_fetch_row(result);
		wr.amount=atof(rows[1]),wr.price=atof(rows[2]),wr.fee=atof(rows[3]);
    }
    else
		return false;
	return true;
}
//电费
bool sql_record_electricity(struct electricity &elty)
{
    char tmp[256];
    sprintf(tmp, "INSERT INTO `electricity`(`record_time`, `amount`, `price`, `fee`) VALUES ('%s', %f, %f, %f);", 
        elty.record_time,elty.amount, elty.price,elty.fee);
     return execQuery(tmp);
}

bool sql_show_electricity(struct electricity &elty)
{
	char tmp[256];
    sprintf(tmp, "select * from `electricity` where record_time = '%s';", elty.record_time);
	const char* sql=tmp;
	if (mysql_query(conn, sql) != 0)	//执行失败返回非0值
    {
        cout<< "Query Error: " << mysql_error(conn);
        return false;
    }
	MYSQL_RES* result = NULL;
    MYSQL_ROW rows;
    result = mysql_store_result(conn);
	if(result != NULL){
		rows = mysql_fetch_row(result);
		elty.amount=atof(rows[1]),elty.price=atof(rows[2]),elty.fee=atof(rows[3]);
    }
    else
		return false;
	return true;
}

//桌子

bool sql_desk_register(struct desk &dk)
{
	char tmp[256];
    sprintf(tmp, "INSERT INTO `desk`(`desk_id`, `model`, `price`, `register_time`) VALUES ('%s', '%s', %f, '%s');", 
        dk.id,dk.model, dk.price,dk.register_time);
    return execQuery(tmp);
}

bool sql_desk_recycle(struct desk &dk)
{
	char tmp[256];
    sprintf(tmp, "UPDATE `desk` SET `state`=0,`recycle_time`='%s',`recycle_price`=%f where `desk_id`='%s';", 
        dk.recycle_time,dk.recycle_price, dk.id);
     return execQuery(tmp);
}

bool sql_show_desk(struct desk &dk)
{
	char tmp[256];
    sprintf(tmp, "select * from `desk` where desk_id = '%s';", dk.id);
	const char* sql=tmp;
	if (mysql_query(conn, sql) != 0)	//执行失败返回非0值
    {
        cout<< "Query Error: " << mysql_error(conn);
        return false;
    }
	MYSQL_RES* result = NULL;
    MYSQL_ROW rows;
    result = mysql_store_result(conn);
	if(result != NULL){
		rows = mysql_fetch_row(result);
		strcpy(dk.model,rows[1]),dk.price=atof(rows[2]),
		strcpy(dk.register_time,rows[3]),dk.state=atoi(rows[4]),
		strcpy(dk.recycle_time,rows[5]),dk.recycle_price=atof(rows[6]);
    }
    else
		return false;
	return true;
}

//椅子
bool sql_chair_register(struct chair &cr)
{
	char tmp[256];
    sprintf(tmp, "INSERT INTO `chair`(`chair_id`, `model`, `price`, `register_time`) VALUES ('%s', '%s', %f, '%s');", 
        cr.id,cr.model, cr.price,cr.register_time);
     return execQuery(tmp);
}

bool sql_chair_recycle(struct chair &cr)
{
	char tmp[256];
    sprintf(tmp, "UPDATE `chair` SET `state`=0,`recycle_time`='%s',`recycle_price`=%f where `chair_id`='%s';", 
        cr.recycle_time,cr.recycle_price, cr.id);
     return execQuery(tmp);
}

bool sql_show_chair(struct chair &cr)
{
	char tmp[256];
    sprintf(tmp, "select * from `chair` where chair_id = '%s';", cr.id);
	const char* sql=tmp;
	if (mysql_query(conn, sql) != 0)	//执行失败返回非0值
    {
        cout<< "Query Error: " << mysql_error(conn);
        return false;
    }
	MYSQL_RES* result = NULL;
    MYSQL_ROW rows;
    result = mysql_store_result(conn);
	if(result != NULL){
		rows = mysql_fetch_row(result);
		strcpy(cr.model,rows[1]),cr.price=atof(rows[2]),
		strcpy(cr.register_time,rows[3]),cr.state=atoi(rows[4]),
		strcpy(cr.recycle_time,rows[5]),cr.recycle_price=atof(rows[6]);
    }
    else
		return false;
	return true;
}

//电脑
bool sql_computer_register(struct computer &cmpr)
{
	char tmp[256];
    sprintf(tmp, "INSERT INTO `computer`(`computer_id`, `model`, `price`, `register_time`) VALUES ('%s', '%s', %f, '%s');", 
        cmpr.id,cmpr.model, cmpr.price,cmpr.register_time);
     return execQuery(tmp);
}

bool sql_computer_recycle(struct computer &cmpr)
{
	char tmp[256];
    sprintf(tmp, "UPDATE `computer` SET `state`=0,`recycle_time`='%s',`recycle_price`=%f where `computer_id`='%s';", 
        cmpr.recycle_time,cmpr.recycle_price, cmpr.id);
     return execQuery(tmp);
}

bool sql_show_computer(struct computer &cmpr)
{
	char tmp[256];
    sprintf(tmp, "select * from `computer` where computer_id = '%s';", cmpr.id);
	const char* sql=tmp;
	if (mysql_query(conn, sql) != 0)	//执行失败返回非0值
    {
        cout<< "Query Error: " << mysql_error(conn);
        return false;
    }
	MYSQL_RES* result = NULL;
    MYSQL_ROW rows;
    result = mysql_store_result(conn);
	if(result != NULL){
		rows = mysql_fetch_row(result);
		strcpy(cmpr.model,rows[1]),cmpr.price=atof(rows[2]),
		strcpy(cmpr.register_time,rows[3]),cmpr.state=atoi(rows[4]),
		strcpy(cmpr.recycle_time,rows[5]),cmpr.recycle_price=atof(rows[6]);
    }
    else
		return false;
	return true;
}

//打印机
bool sql_printer_register(struct printer &pinr)
{
	char tmp[256];
    sprintf(tmp, "INSERT INTO `printer`(`printer_id`, `model`, `price`, `register_time`) VALUES ('%s', '%s', %f, '%s');", 
        pinr.id,pinr.model, pinr.price,pinr.register_time);
     return execQuery(tmp);
}

bool sql_printer_recycle(struct printer &pinr)
{
	char tmp[256];
    sprintf(tmp, "UPDATE `printer` SET `state`=0,`recycle_time`='%s',`recycle_price`=%f where `printer_id`='%s';", 
        pinr.recycle_time,pinr.recycle_price, pinr.id);
     return execQuery(tmp);
}

bool sql_show_printer(struct printer &pinr)
{
	char tmp[256];
    sprintf(tmp, "select * from `printer` where printer_id = '%s';", pinr.id);
	const char* sql=tmp;
	if (mysql_query(conn, sql) != 0)	//执行失败返回非0值
    {
        cout<< "Query Error: " << mysql_error(conn);
        return false;
    }
	MYSQL_RES* result = NULL;
    MYSQL_ROW rows;
    result = mysql_store_result(conn);
	if(result != NULL){
		rows = mysql_fetch_row(result);
		strcpy(pinr.model,rows[1]),pinr.price=atof(rows[2]),
		strcpy(pinr.register_time,rows[3]),pinr.state=atoi(rows[4]),
		strcpy(pinr.recycle_time,rows[5]),pinr.recycle_price=atof(rows[6]);
    }
    else
		return false;
	return true;
}


//工资
bool sql_input_minimum_fixed(struct salary &slry)
{
	char tmp[256];
	sprintf(tmp, "truncate table salary;");
	execQuery(tmp);
    sprintf(tmp, "insert into salary(staff_id) select staff_id from staff;");
	execQuery(tmp);
	sprintf(tmp, "update salary set minimum=%f,fixed=%f;",slry.minimum,slry.fixed);
    return execQuery(tmp);
}

bool sql_input_bonus(struct salary &slry)
{
	char tmp[256];
    sprintf(tmp, "insert into month_salary values ('%s','%s',null,%f,null);",slry.unsy.id,slry.unsy.ymd,slry.unsy.bonus);
	//sprintf(tmp, "update month_salary set bonus=%f,month='%s' where staff_id='%s';",slry.unsy.bonus,slry.unsy.ymd,slry.unsy.id);
    return execQuery(tmp);
}

bool sql_get_bonus_fixed_min(struct salary &slry)//通过id、日期获取奖金、固定工资、最低工资
{
	char tmp[256];
    sprintf(tmp, "select bonus from `month_salary` where (staff_id='%s' and month='%s');"
    , slry.unsy.id,slry.unsy.ymd);
	const char* sql=tmp;
	if (mysql_query(conn, sql) != 0)	//执行失败返回非0值
    {
        cout<< "Query Error: " << mysql_error(conn);
        return false;
    }
	MYSQL_RES* result = NULL;
    MYSQL_ROW rows;
    result = mysql_store_result(conn);
	if(result != NULL){
		rows = mysql_fetch_row(result);
		slry.unsy.bonus=atof(rows[0]);
    }
    else
		return false;
    sprintf(tmp, "select fixed,minimum from `salary` where staff_id='%s';"
    , slry.unsy.id);
	sql=tmp;
	if (mysql_query(conn, sql) != 0)	//执行失败返回非0值
    {
        cout<< "Query Error: " << mysql_error(conn);
        return false;
    }
	result = NULL;
    result = mysql_store_result(conn);
	if(result != NULL){
		rows = mysql_fetch_row(result);
		slry.fixed=atof(rows[0]);
        slry.minimum=atof(rows[1]);
    }
    else
		return false;
	return true;
}

bool sql_get_performance(struct salary &slry)
{
	char tmp[256];
	sprintf(tmp, "update month_salary set performance=%f where (staff_id='%s' and month='%s');"
    ,slry.unsy.performance,slry.unsy.id,slry.unsy.ymd);
    return execQuery(tmp);
}

bool sql_compu_salary(struct salary &slry)
{
	char tmp[256];
	sprintf(tmp, "update month_salary set total=%f where (staff_id='%s' and month='%s');"
    ,slry.unsy.total,slry.unsy.id,slry.unsy.ymd);
    return execQuery(tmp);
}

bool sql_show_salary(struct salary &slry)
{
	char tmp[256];
    sprintf(tmp, "select total from `month_salary` where (staff_id='%s' and month='%s');"
    , slry.unsy.id,slry.unsy.ymd);
	const char* sql=tmp;
	if (mysql_query(conn, sql) != 0)	//执行失败返回非0值
    {
        cout<< "Query Error: " << mysql_error(conn);
        return false;
    }
	MYSQL_RES* result = NULL;
    MYSQL_ROW rows;
    result = mysql_store_result(conn);
    rows = mysql_fetch_row(result);
	if(result != NULL && rows != 0){
		// rows = mysql_fetch_row(result);
		slry.unsy.total=atof(rows[0]);
    }
    else
		return false;
	return true;
}

//给财务系统读取worker_earnings接口
bool sql_get_performance(string  &id,float &p)//p performance, writed by abing, wz add
{
    string msg;
    char tmp[256];
    sprintf(tmp, "select SUM(`worker_earnings`) from `have` where `staff_id` = '%s' ;", id.c_str());
    if(!execQuery(tmp, &msg)) return false; 
    string t = msg.c_str();
    p = atof(t.c_str());
    return true;
}